/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sarco.bwi.lib;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

/**
 *
 * @author iktsup
 */
public class Mysql {

    /**
     * @deprecated v0.0.5a
     */
    public static class Columns {

        public static final String NODE_IDNODE = "idnode";
        public static final String NODE_CURR_FRAME = "curr_frame";
        public static final String NODE_END_FRAME = "end_frame";
        public static final String NODE_PERCENT_DONE = "percent_done";
        public static final String NODE_START_FRAME = "start_frame";
        public static final String NODE_SYSTEM = "system";
        public static final String NODE_TIME_LEFT = "time_left";
        public static final String NODE_HOSTNAME = "hostname";
        public static final String LOG_IDLOG = "idlog";
        public static final String LOG_IDNODE = "idnode";
        public static final String LOG_DATE = "date";
        public static final String LOG_CURR_FRAME = "curr_frame";
        public static final String LOG_END_FRAME = "end_frame";
        public static final String LOG_PERCENT_DONE = "percent_done";
        public static final String LOG_START_FRAME = "start_frame";
        public static final String JOBS_IDJOBS = "idjobs";
        public static final String JOBS_IDNODE = "idnode";
        public static final String JOBS_SFRAME = "sframe";
        public static final String JOBS_EFRAME = "eframe";
        public static final String JOBS_IS_TAKEN = "is_taken";
    }
    
    /**
     * Converts the given <code>Date</code> to a Mysql datetime format 
     * (yyyy-mm-dd hh:mm:ss) <code>String</code>.
     * @param date The <code>Date</code> to convert.
     * @since v0.0.5a
     * @see Date
     * @see Calendar
     * @return The mysql formatted datetime <code>String</code> if the 
     * <code>date</code> variable does not equal to <code>null</code>, else
     * it returns <code>null</code>.
     */
    public static String convertDateToMysqlDatetimeString(Date date) {
        if (date != null) {
            Calendar cal = Calendar.getInstance();
            cal.clear();
            cal.setTime(date);
            String datetime =
                    cal.get(Calendar.YEAR) + "-"
                    + cal.get(Calendar.MONTH) + "-"
                    + cal.get(Calendar.DAY_OF_MONTH) + " "
                    + cal.get(Calendar.HOUR_OF_DAY) + ":"
                    + cal.get(Calendar.MINUTE) + ":"
                    + cal.get(Calendar.SECOND);
            
            return datetime;
        }
        return null;
    }

    /**
     *  @deprecated v0.0.5a
     */
    public static final class Tables {

        public static final String NODE = "node";
        public static final String LOG = "log";
        public static final String JOBS = "jobs";
    }
    /**
     *  @deprecated v0.0.5a
     */
    private static final String SQL_GET_NODE_DATA = "SELECT * FROM "
            + Tables.NODE;
    /**
     *  @deprecated v0.0.5a
     */
    private static final String SQL_GET_LOG_DATA = "SELECT * FROM "
            + Tables.LOG;
    /**
     *  @deprecated v0.0.5a
     */
    private static final String SQL_GET_JOBS_DATA = "SELECT * FROM "
            + Tables.JOBS;
    private static final String SQL_SPLIT_ROW = "##";
    private static final String SQL_SPLIT_COL = "::";
    private Connection conn;
    private Statement stmnt;
    private ResultSet rs;
    private Node[] nodes;
    public static final String DEFAULT_DB = "bwi";
    public static final String DEFAULT_USER = "root";
    public static final String DEFAULT_PASS = "";
    public static final String DEFAULT_HOST = "127.0.0.1";

    /**
     * The default constructor
     */
    public Mysql() {
        this.conn = null;
        this.stmnt = null;
        this.rs = null;
        this.nodes = null;
    }

    /**
     * Connects to the Mysql database
     * @param host The hostname/ip of the mysql database server
     * (Default: '127.0.0.1')
     * @param user The user to use with the querying (Default: 'root')
     * @param password The password for the mysql user (Default: '')
     * @param database The mysql database ti query (Default: 'bwi')
     * @see com.sarco.bwi.lib.mysql.Misc
     */
    public void connect(String host,
            String user,
            String password,
            String database) {
        String connection = "jdbc:mysql://" + host + "/" + database + "?"
                + "user=" + user + "&password=" + password;
        try {
            this.conn = DriverManager.getConnection(connection);
            this.stmnt = conn.createStatement();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public Job[] getJobData() throws SQLException {
        rs = stmnt.executeQuery(
                "SELECT * FROM " + TJob.TABLE_NAME);
        ArrayList<Job> jobs = new ArrayList<Job>();

        while (rs.next()) {
            Job j = new Job();
            j.setAbortrequested(rs.getBoolean(TJob.COL_ABORTREQUESTED));
            j.setClientcpu(rs.getString(TJob.COL_CLIENTCPU));
            j.setClientcpupower(rs.getString(TJob.COL_CLIENTCPUPOWER));
            j.setClienthostname(rs.getString(TJob.COL_CLIENTHOSTNAME));
            j.setClientip(rs.getString(TJob.COL_CLIENTIP));
            j.setClientmemory(rs.getString(TJob.COL_CLIENTMEMORY));
            j.setClientos(rs.getString(TJob.COL_CLIENTOS));
            j.setEndframe(rs.getInt(TJob.COL_ENDFRAME));
            j.setIdjob(rs.getInt(TJob.COL_IDJOB));
            j.setPercentdone(rs.getInt(TJob.COL_PERCENTDONE));
            j.setStartframe(rs.getInt(TJob.COL_STARTFRAME));
            j.setTaken(rs.getBoolean(TJob.COL_TAKEN));
            j.setTimefinished(rs.getDate(TJob.COL_TIMEFINISHED));
            j.setTimeleft(rs.getString(TJob.COL_TIMELEFT));
            j.setTimestarted(rs.getDate(TJob.COL_TIMESTARTED));

            jobs.add(j);
        }

        Job[] aJobs = new Job[jobs.size()];
        for (int i = 0; i < aJobs.length; i++) {
            aJobs[i] = jobs.get(i);
        }

        return aJobs;
    }
    /**
     * Gets a <code>Job</code> object based on it's id ('idjob')
     * @return The <code>Job</code> accosiated with that id, or 
     * <code>null</code> if no job with that id is found.
     */
    public Job getJobEntry(int id) throws SQLException {
        rs = stmnt.executeQuery(
                "SELECT * FROM " + TJob.TABLE_NAME + "\n" +
                "WHERE " + TJob.COL_IDJOB + "=" + id);
        /**
         * Selects the first one found:
         */
        if (rs.next()) {            
            Job j = new Job();
            j.setAbortrequested(rs.getBoolean(TJob.COL_ABORTREQUESTED));
            j.setClientcpu(rs.getString(TJob.COL_CLIENTCPU));
            j.setClientcpupower(rs.getString(TJob.COL_CLIENTCPUPOWER));
            j.setClienthostname(rs.getString(TJob.COL_CLIENTHOSTNAME));
            j.setClientip(rs.getString(TJob.COL_CLIENTIP));
            j.setClientmemory(rs.getString(TJob.COL_CLIENTMEMORY));
            j.setClientos(rs.getString(TJob.COL_CLIENTOS));
            j.setEndframe(rs.getInt(TJob.COL_ENDFRAME));
            j.setIdjob(rs.getInt(TJob.COL_IDJOB));
            j.setPercentdone(rs.getInt(TJob.COL_PERCENTDONE));
            j.setStartframe(rs.getInt(TJob.COL_STARTFRAME));
            j.setTaken(rs.getBoolean(TJob.COL_TAKEN));
            j.setTimefinished(rs.getDate(TJob.COL_TIMEFINISHED));
            j.setTimeleft(rs.getString(TJob.COL_TIMELEFT));
            j.setTimestarted(rs.getDate(TJob.COL_TIMESTARTED));
            
            return j;
        }
        return null;
    }

    public void updateJobEntry(Job job) throws SQLException, ParseException {
        String timestarted = 
                (job.getTimestarted() != null) ? 
                Mysql.convertDateToMysqlDatetimeString(job.getTimestarted()) :
                Mysql.convertDateToMysqlDatetimeString(new Date(0));
        String timefinished = 
                (job.getTimefinished() != null) ?
                Mysql.convertDateToMysqlDatetimeString(job.getTimefinished()) :
                Mysql.convertDateToMysqlDatetimeString(new Date(0));

        String sql =
                "UPDATE " + TJob.TABLE_NAME + "\n"
                + "SET "
                + ((!job.isTaken()) ? TJob.COL_CLIENTCPU + "='" + job.getClientcpu() + "', " : "")
                + ((!job.isTaken()) ? TJob.COL_CLIENTCPUPOWER + "='" + job.getClientcpupower() + "', " : "")
                + ((!job.isTaken()) ? TJob.COL_CLIENTHOSTNAME + "='" + job.getClienthostname() + "', " : "")
                + ((!job.isTaken()) ? TJob.COL_CLIENTIP + "='" + job.getClientip() + "', " : "")
                + ((!job.isTaken()) ? TJob.COL_CLIENTMEMORY + "='" + job.getClientmemory() + "', " : "")
                + ((!job.isTaken()) ? TJob.COL_CLIENTOS + "='" + job.getClientos() + "', " : "")
                + TJob.COL_PERCENTDONE + "=" + job.getPercentdone() + ", "
                + TJob.COL_TAKEN + "=" + job.isTaken() + ", "
                + TJob.COL_TIMEFINISHED + "='" + timefinished + "', "
                + TJob.COL_TIMELEFT + "='" + job.getTimeleft() + "'"
                + ((!job.isTaken()) ? ", " + TJob.COL_TIMESTARTED + "='" + timestarted + "'" : "") + "\n"
                + "WHERE " + TJob.COL_IDJOB + "=" + job.getIdjob() + ";";
        stmnt.execute(sql);
    }

    /**
     * Fetches the current node data, you need to manually invoke 
     * <code>new Mysql().getNodes();</code><br>
     * Example:<br>
     * <code>
     * Mysql mysql = new Mysql();<br>
     * mysql.fetchNodeData();<br>
     * Node[] nodes = mysql.getNodes();
     * </code><br>
     * It throws a <code>java.sql.SQLException</code>
     * @see com.sarco.bwi.lib.Mysql
     * @see java.sql.Statement
     * @see java.sql.ResultSet
     * @see java.sql.SQLException
     * @deprecated v0.0.5a
     */
    public void fetchNodeData() throws SQLException {
        String[] data = getNodeData(stmnt.executeQuery(SQL_GET_NODE_DATA));
        Node[] sqlnodes = new Node[data.length];
        if (data != null) {
            for (int i = 0; i < data.length; i++) {
                Node n = new Node();
                String[] sData = data[i].split(SQL_SPLIT_COL);

                sqlnodes[i] = n;
            }

            this.setNodes(sqlnodes);
        }
    }

    public void closeConnection() throws SQLException {
        if (this.rs != null) {
            this.rs.close();
        }
        if (this.stmnt != null) {
            this.stmnt.close();
        }
        if (this.conn != null) {
            this.conn.close();
        }
        this.rs = null;
        this.stmnt = null;
        this.conn = null;
    }

    /**
     * @deprecated v0.0.5a
     * @param rs
     * @return
     * @throws SQLException 
     */
    private String[] getNodeData(ResultSet rs) throws SQLException {
        String s = "";
        while (rs.next()) {
            int id = rs.getInt(Mysql.Columns.NODE_IDNODE);
            int cf = rs.getInt(Mysql.Columns.NODE_CURR_FRAME);
            int ef = rs.getInt(Mysql.Columns.NODE_END_FRAME);
            int pd = rs.getInt(Mysql.Columns.NODE_PERCENT_DONE);
            int sf = rs.getInt(Mysql.Columns.NODE_START_FRAME);
            String sy = rs.getString(Mysql.Columns.NODE_SYSTEM);
            String tl = rs.getString(Mysql.Columns.NODE_TIME_LEFT);
            s += id + Mysql.SQL_SPLIT_COL
                    + cf + Mysql.SQL_SPLIT_COL
                    + ef + Mysql.SQL_SPLIT_COL
                    + pd + Mysql.SQL_SPLIT_COL
                    + sf + Mysql.SQL_SPLIT_COL
                    + sy + Mysql.SQL_SPLIT_COL
                    + tl + Mysql.SQL_SPLIT_ROW;
        }

        if (s.length() > 0 && s.contains(Mysql.SQL_SPLIT_ROW)) {
            return s.split(Mysql.SQL_SPLIT_ROW);
        } else {
            return null;
        }
    }

    /**
     * @deprecated v0.0.5a
     * @return 
     */
    public Node[] getNodes() {
        return nodes;
    }

    /**
     * @deprecated v0.0.5a
     * @param nodes 
     */
    public void setNodes(Node[] nodes) {
        this.nodes = nodes;
    }

    /**
     * Gets the <code>java.sql.Statement</code>
     * @return the <code>java.sql.Statement</code>
     * @see java.sql.Statement
     */
    public Statement getStatement() {
        return stmnt;
    }
}
